if exists (select 1
          from sysobjects
          where  id = object_id('bd_proc_practical_working')
          and type in ('P','PC'))
   drop procedure bd_proc_practical_working
go


create procedure bd_proc_practical_working (@date_begin date,@date_end date,@depts varchar(max) = '') as
BEGIN
	--部门人员和工时（实际）统计lzl
	--思路：1、创建返回临时表#returnTable
				--2、将depts分解插入#returnTable
				--3将参与统计的数据放入#temp_bd_kq_timecard
				--4、分别按部门分组加不同的条件，统计出指定数据 LEFT JOIN 全部查询语句，部门id关联，将指定数据更新到#returnTable
				--5、返回#returnTable；

	--1、创建返回临时表#returnTable
	-- 科室	depName
	-- 正常出勤人数	normalNum
	-- 加班人数	otNum
	-- 放班人数	fbNum
	-- 请假人数	leaveNum
	-- 迟到人数	tardyNum
	-- 早退人数	leaveEarlyNum
	-- 实出工时	practicalHours
	-- 加班工时	otHours
	-- 放班工时	fbHours
	-- 请假工时	leaveHours
	-- 迟到工时	tardyHours
	-- 早退工时	leaveEarlyHours
	-- 缺勤工时（含请假流程中）	absenceHours
	create table #returnTable(
		dep_serial bigint,
		dep_no varchar(20),
		dep_name varchar(200) COLLATE Chinese_PRC_CI_AS,
		normal_num int,
		ot_num int,
		fb_num int,
		leave_num int,
		tardy_num int,
		leave_early_num int,
		practical_hours decimal(18,2),
		ot_hours decimal(18,2),
		fb_hours decimal(18,2),
		leave_hours decimal(18,2),
		tardy_hours decimal(18,2),
		leave_early_hours decimal(18,2),
		absence_hours decimal(18,2),
	)

	--2、将depts分解插入#returnTable
	insert into #returnTable(dep_serial) select * from string_split(@depts,',');

	--3将参与统计的数据放入#temp_bd_kq_timecard
	SELECT * into #temp_bd_kq_timecard  FROM  bd_kq_timecard
	WHERE dept_id in (select dep_serial from #returnTable) and card_date BETWEEN @date_begin and @date_end


	--4、分别按部门分组加不同的条件，统计出指定数据 LEFT JOIN 全部查询语句，部门id关联，将指定数据更新到#returnTable
	update r
		set
				dep_name = d.dep_name,
				dep_no = d.dep_no,
				normal_num = isnull(a1.normal_num,0),
				ot_num  = isnull(a2.ot_num,0),
				fb_num = isnull(a3.fb_num,0),
				leave_num = isnull(a4.leave_num,0),
				tardy_num = isnull(a5.tardy_num,0),
				leave_early_num = isnull(a6.leave_early_num,0),
				practical_hours = isnull(a7.practical_hours,0),
				ot_hours = isnull(a8.ot_hours,0),
				fb_hours = isnull(a9.fb_hours,0),
				leave_hours = isnull(a10.leave_hours,0),
				tardy_hours = isnull(a11.tardy_hours,0),
				leave_early_hours = isnull(a12.leave_early_hours,0),
				absence_hours = isnull(a13.absence_hours,0)
	from #returnTable r
	inner join dt_dep d on d.dep_serial = r.dep_serial
	--4.1,统计部门正常出勤人数
	left join
	(select dept_id,count(1) as normal_num
		from (select DISTINCT dept_id,user_serial from #temp_bd_kq_timecard  where shift_id > 0 and status = 0) x
		GROUP BY dept_id) a1 on a1.dept_id  = r.dep_serial

	--4.2 统计部门加班人数
	left join
	(select dept_id,count(1) as ot_num
		from (select DISTINCT dept_id,user_serial from #temp_bd_kq_timecard  where ot_pending_hours>0) x
		GROUP BY dept_id) a2 on a2.dept_id  = r.dep_serial

	--4.3 统计部门放班人数
	left join
	(select dept_id,count(1) as fb_num
		from (select DISTINCT dept_id,user_serial from #temp_bd_kq_timecard  where off_duty_hour>0) x
		GROUP BY dept_id) a3 on a3.dept_id  = r.dep_serial

	--4.4 统计部门请假人数
	left join
	(select dept_id,count(1) as leave_num
		from (select DISTINCT dept_id,user_serial from #temp_bd_kq_timecard  where leave_hours_total>0 and off_duty_hour=0) x
		GROUP BY dept_id) a4 on a4.dept_id  = r.dep_serial

	--4.5 统计部门迟到人数
	left join
	(select dept_id,count(1) as tardy_num
		from (select DISTINCT dept_id,user_serial from #temp_bd_kq_timecard  where status=4) x
		GROUP BY dept_id) a5 on a5.dept_id  = r.dep_serial

	--4.6 统计部门早退人数
	left join
	(select dept_id,count(1) as leave_early_num
		from (select DISTINCT dept_id,user_serial from #temp_bd_kq_timecard  where status=5) x
		GROUP BY dept_id) a6 on a6.dept_id  = r.dep_serial

	--4.7----4.13 若不拓展其他逻辑或条件，可以放在一起统计。
	--4.7统计部门实出工时
	left join
	(select dept_id,sum(isnull(actual_hours,0))/60.0 as practical_hours from #temp_bd_kq_timecard group by dept_id) a7 on a7.dept_id  = r.dep_serial

	--4.8统计部门加班工时
	left join
	(select dept_id,sum(isnull(ot_pending_hours,0))/60.0 as ot_hours from #temp_bd_kq_timecard group by dept_id) a8 on a8.dept_id  = r.dep_serial

	--4.9统计部门放班工时
	left join
	(select dept_id,sum(isnull(off_duty_hour,0))/60.0 as fb_hours from #temp_bd_kq_timecard group by dept_id) a9 on a9.dept_id  = r.dep_serial

	--4.10统计部门请假工时，总请假工时-放班工时
	left join
	(select dept_id,sum(isnull(leave_hours_total,0)-isnull(off_duty_hour,0))/60.0 as leave_hours from #temp_bd_kq_timecard group by dept_id) a10 on a10.dept_id  = r.dep_serial

	--4.11统计部门迟到工时
	left join
	(select dept_id,sum(isnull(late_hour,0))/60.0 as tardy_hours from #temp_bd_kq_timecard group by dept_id) a11 on a11.dept_id  = r.dep_serial

	--4.12统计部门早退工时
	left join
	(select dept_id,sum(isnull(early_hour,0))/60.0 as leave_early_hours from #temp_bd_kq_timecard group by dept_id) a12 on a12.dept_id  = r.dep_serial

	--4.13统计部门缺勤工时，标准工时-实出工时
	left join
	(select dept_id,sum(isnull(should_hours,0)-isnull(actual_hours,0))/60.0 as absence_hours from #temp_bd_kq_timecard group by dept_id) a13 on a13.dept_id  = r.dep_serial

	--5、返回#returnTable；
	select * from #returnTable order by dep_no


END
go
